﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RPSImporter.Infrastructure;
using DB;
using System.Data;
using System.Configuration;
using RPSImporter.Services;
using System.Reflection;
using System.Data.OleDb;
using System.IO;
using RPSImporter.Models.Settings;

namespace RPSImporter.Models.Importers
{
   public class XLSImporter : IImporter
   {
      public IFile File { get; set; }
      public GeneralSettings Settings { get; set; }
      public string Error { get; set; }
      public IList<string> Messages { get; set; }

      public XLSImporter(IFile file)
      {
         this.File = file;
         this.Settings = new GeneralSettings();
         this.Messages = new List<string>();
      }

      public bool Import()
      {
         return this.Import(null);
      }

      public bool Import(string fileType)
      {
         this.Messages.Clear();
         try
         {
            string consStr = String.Format(ConfigurationManager.ConnectionStrings["xls"].ConnectionString, this.File.FullName);
            ExcelDB.Instance.openDatabase(consStr);

            string newFileName = this.File.FullName.Split('.')[0] + "_.xls";
            using (Stream stream = System.IO.File.Open(this.File.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
               using (Stream newStream = System.IO.File.Open(newFileName, FileMode.Create, FileAccess.ReadWrite))
               {
                  stream.CopyTo(newStream);
               }
            }

            var dbSchema = ExcelDB.Instance.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string tableName = dbSchema.Rows[0].Field<string>("TABLE_NAME");

            DataTable dt = ExcelDB.Instance.getDataTable("SELECT * FROM [" + tableName + "]");

            var fileName = newFileName.Split('.')[0].Split('\\').Last();
            if (fileType != null)
            {
               Type type = Util.GetFileType(fileType);
               if (type != null)
               {
                  DatabaseSvc.Instance.Import(dt, type);
               }
            }
            else
            {
               if (fileName == this.Settings.CompanyFileName)
               {
                  DatabaseSvc.Instance.Import<Company>(dt);
               }
               else if (fileName == this.Settings.EnterpriseFileName)
               {
                  DatabaseSvc.Instance.Import<Enterprise>(dt);
               }
               else if (fileName == this.Settings.ExternalEnterprisesFileName)
               {
                  DatabaseSvc.Instance.Import<EnterpriseExt>(dt);
               }
               else if (fileName == this.Settings.HierarchyFileName)
               {
                  DatabaseSvc.Instance.Import<Hierarchy>(dt);
               }
               else if (fileName == this.Settings.PersonsFileName)
               {
                  DatabaseSvc.Instance.Import<Person>(dt);
               }
               else if (fileName == this.Settings.HoursPersonFileName)
               {
                  DatabaseSvc.Instance.Import<HoursPerson>(dt);
               }
               else if (fileName == this.Settings.HoursHierarchyFileName)
               {
                  DatabaseSvc.Instance.Import<HoursHierarchy>(dt);
               }
            }
            this.Messages = DatabaseSvc.Instance.Messages;
            return true;
         }
         catch (Exception ex)
         {
            this.Error = ex.Message;
            return false;
         }
         finally
         {
            ExcelDB.Instance.Connection.Close();
         }
      }
   }
}